<?php
declare (strict_types=1);

namespace app\db;

use app\util\Tool;
use think\Config;
use think\facade\Db;
use think\Model;


class BigDb
{
    public function comDbfindtheme($uniacid, $id)
    {

        //$tool = new Tool();
        //ims_water_salegroup3_theme
        $needu = 'neeedu';
        $needi = 'needi';
        $orginsql = "SELECT id,uniacid,uid,title,paynum,begintime,endtime,finishsum,groupsum,fanssum  from ims_water_salegroup3_theme  where uniacid = neeedu and  id = needi  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needi, $id, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
            $begintime = $sqldata[0]['begintime'];
            $begintime = time();
            $endtime = $sqldata[0]['endtime'];
            //countdown  两个时间戳
            $datestr = $this->timediff2($begintime, $endtime);
            //$arr['datestr'] = $datestr;
            $arr['vdata']['datestr'] = $datestr;;
        } else {
            $arr['vdata'] = null;
        }

        $isDebug = \think\facade\App::isDebug();
        if ($isDebug) {
            $arr['actionsql1'] = $actionsql;
        }
        $arr['ask'] = "theme表";
        $arr['remark'] = "合并DB";

        //新需求 就是改了 paynum
//        $newpaynumarr = this->findHomeOrderNum($uniacid, $id);
//        if($newpaynumarr['vdata'] !== null){
//            $arr['vdata']['paynum'] = $newpaynumarr['vdata']['newpaynum'];
//        }
        return $arr;

    }

    //findHomeOrderNum

    public function findHomeOrderNum($uniacid, $themeid){
        $needu = 'neeedu';
        $needi = 'needi';
        $orginsql = "SELECT  count(id) as newpaynum  from ims_water_salegroup3_record  where uniacid = neeedu and  themeid = needi  and (status = 1 or status = 2 ) ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needi, $themeid, $orginsql);  $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        }else{
            $arr['vdata'] = null;
        }
        $arr['ask'] = "record表";
        $arr['remark'] = "新记录";
        return $arr;
    }



    /**
     *
     *  1活动标题
     * ims_water_salegroup3_theme表格中按照uniacid和id检索，获取title一项目值
     */
    public function activitietitle($uniacid, $id)
    {
        $needu = 'neeedu';
        $needi = 'needi';
        $orginsql = "SELECT id,uniacid,uid,title from ims_water_salegroup3_theme  where uniacid = neeedu and  id = needi  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needi, $id, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);


        $arr['sql'] = $actionsql;
        $arr['sqldata'] = $sqldata;
        $arr['vdata'] = $sqldata[0];
        $arr['ask'] = "活动标题";
        $arr['remark'] = "活动标题";
        return $arr;
    }

    //ordersum
    public function ordersum($uniacid, $id)
    {

        $needu = 'neeedu';
        $needi = 'needi';
        $orginsql = "SELECT id,uniacid,uid,title,paynum from ims_water_salegroup3_theme  where uniacid = neeedu and  uid = needi  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needi, $id, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;

        $arr['ask'] = "订单总数";
        $arr['remark'] = "订单总数";
        return $arr;
    }

    /**
     * 准客数量
     * 备注，因为是带 status查询 所以暂时还合并不了
     * @return mixed
     */
    public function zhuncus($uniacid, $themeid)
    {

        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "select count(*) as num from  ims_water_salegroup3_record where uniacid = neeedu and  themeid = needt  and `status` = 0 ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;
        $arr['ask'] = "准客数量";
        $arr['remark'] = "准客数量";
        return $arr;
    }

    public function ut($uniacid, $themeid)
    {

        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "select count(*) as num from  ims_water_salegroup3_record where uniacid = neeedu and  themeid = needt  and `status` = 0 ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;
        $arr['ask'] = "ut";
        $arr['remark'] = "";
        return $arr;
    }

    //已支付 payon 参数
    public function payon($uniacid, $themeid)
    {

        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "select count(*) as num from  ims_water_salegroup3_record where uniacid = neeedu and  themeid = needt  and  (`status` = 1 or `status` = 2 )  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;
        $arr['ask'] = "ut";
        $arr['remark'] = "已支付";
        return $arr;
    }


    //newcus
    public function newoldcus($uniacid, $themeid, $newold)
    {
        //str_replace need开头的替换成入参
        $neednewold = 'neednewold';
        $needu = 'neeedu';
        $needt = 'needt';
        //$orginsql = "select count(*) as num from  ims_water_salegroup3_record where uniacid = neeedu and  themeid = needt  and `status` = 0 ";
        $orginsql = "select count(*) as num from  ims_water_salegroup3_record where uniacid = neeedu and  themeid = needt  and  (`status` = 1 or `status` = 2 ) and  `option` LIKE CONCAT('%', 'neednewold', '%')";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $orginsql = str_ireplace($neednewold, $newold, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);

        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;

        $arr['ask'] = "newoldcus";
        $arr['remark'] = "";
        return $arr;
    }


    /**
     *
     * ims_water_salegroup3_theme表格中按照uniacid和id检索，获取title一项目值
     */
    public function fanssum($uniacid, $id)
    {

        $needu = 'neeedu';
        $needi = 'needi';
        $orginsql = "SELECT id,uniacid,uid,fanssum from ims_water_salegroup3_theme  where uniacid = neeedu and  id = needi  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needi, $id, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;
        $arr['ask'] = "访客数量";
        $arr['remark'] = "访客数量";
        return $arr;
    }


    //countdown
    public function countdown($uniacid, $id)
    {
        //https://blog.csdn.net/weixin_34082177/article/details/94726075
        $tool = new Tool();
        //ims_water_salegroup3_theme
        $needu = 'neeedu';
        $needi = 'needi';
        $orginsql = "SELECT id,uniacid,uid,title,paynum,begintime,endtime from ims_water_salegroup3_theme  where uniacid = neeedu and  id = needi  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needi, $id, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
            //$begintime = $sqldata[0]['begintime'];
            $begintime = time();
            $endtime = $sqldata[0]['endtime'];
            //countdown  两个时间戳
            $datestr = $this->timediff2($begintime, $endtime);
            $arr['datestr'] = $datestr;
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;

        $arr['ask'] = "订单总数";
        $arr['remark'] = "订单总数";
        return $arr;
    }

    //fullgourp 满团数 总团数 ui
    public function fullgourp($uniacid, $id)
    {
        //https://blog.csdn.net/weixin_34082177/article/details/94726075
        $tool = new Tool();
        //ims_water_salegroup3_theme
        $needu = 'neeedu';
        $needi = 'needi';
        $orginsql = "SELECT id,uniacid,uid,title,paynum,begintime,endtime,finishsum,groupsum from ims_water_salegroup3_theme  where uniacid = neeedu and  id = needi  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needi, $id, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;

        $arr['ask'] = "满团与总团";
        $arr['remark'] = "满团与总团";
        return $arr;
    }

    public function ui($uniacid, $id)
    {

        $needu = 'neeedu';
        $needi = 'needi';
        $orginsql = "SELECT id,uniacid,uid,title,paynum,begintime,endtime,finishsum,groupsum from ims_water_salegroup3_theme  where uniacid = neeedu and  id = needi  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needi, $id, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = array();
        }
        $arr['sql'] = $actionsql;

        $arr['asktable'] = "ims_water_salegroup3_theme";
        $arr['remark'] = "暂时没有备注";
        return $arr;
    }





    //worker - u t
    // 员工有多少员工就抓多少员工
    public function worker($uniacid, $themeid)
    {
        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            //这个员工排行，因此最好是10条，但是有可能是0-1条
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;
        $arr['ask'] = "worker 表";
        $arr['remark'] = "";
        return $arr;
    }

    //测试接口
    //fan表  拉新数 - u t
    public function fans($uniacid, $themeid)
    {
        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "select id,openid,count(id) as qiu_count  from ims_water_salegroup3_fans where uniacid = 1 GROUP BY  openid  order by qiu_count Desc limit 10";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        if (count($sqldata) > 0) {
            $arr['sqldata'] = $sqldata;
            //注意这个结果集是可能多条;且大部分情况是应该是10
            $arr['vdata'] = $sqldata[0];
        } else {
            $arr['vdata'] = null;
        }
        $arr['sql'] = $actionsql;
        $arr['ask'] = "worker 表";
        $arr['remark'] = "";
        return $arr;
    }


    //员工排行榜
    //workertop
    public function workertop($uniacid, $themeid)
    {
        //抓所有员工个数 -- ims_water_salegroup3_worker
        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "select id,openid,username,groups from ims_water_salegroup3_worker where `status` = 1 and  uniacid = neeedu and  themeid = needt  group by openid";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        $worker_length = count($sqldata); //所有人 比如100个; 那么在record里很有可能 每人10个订单; 差不多有1000条数据 ；但是sql只要查 计数 例如A号员工 10个直推订单
        $instr = "";
        $countlength = count($sqldata);
        $arr['length_worker'] = $worker_length;
        if ($countlength > 0) {
            //$arr['sqldata'] = $sqldata;

            for ($i = 0; $i < $worker_length; $i++) {
                $instr .= "'" . $sqldata[$i]['openid'] . "'" . ',';
            }
            $instr = substr($instr, 0, -1);
            $arr['instr'] = $instr;

            //在 record 里把所有直推订单搜处理
            $sql_zhi_record = " select id,openid,fopenid,count(id) as zhi_count  from ims_water_salegroup3_record where uniacid = neeedu  and themeid = needt and (`status` = 1 or `status` = 2 ) and  (fopenid is not null  and fopenid <>'')  and fopenid in ($instr) GROUP BY  fopenid  order by zhi_count ";
            $sql_zhi_record = str_ireplace($needu, $uniacid, $sql_zhi_record);
            $sql_zhi_record = str_ireplace($needt, $themeid, $sql_zhi_record);

            $sql_zhi_record_sqldata = \think\facade\Db::query($sql_zhi_record);
            $arr['sql_zhi_record_sqldata'] = $sql_zhi_record_sqldata;
            $length_zhi = count($sql_zhi_record_sqldata);
            $arr['length_zhi'] = $length_zhi;


            //在 record 里把所有溯源订单搜出来
            $sql_su_record = " select id,sourceid,count(id) as su_count  from ims_water_salegroup3_record where uniacid = neeedu  and themeid = needt and (`status` = 1 or `status` = 2 ) and (sourceid is not null and sourceid <>'') and sourceid in ($instr) GROUP BY  sourceid  order by su_count ";
            $sql_su_record = str_ireplace($needu, $uniacid, $sql_su_record);
            $sql_su_record = str_ireplace($needt, $themeid, $sql_su_record);

            $sql_su_record_sqldata = \think\facade\Db::query($sql_su_record);
            $arr['sql_su_record_sqldata'] = $sql_su_record_sqldata;
            $length_su = count($sql_su_record_sqldata);
            $arr['length_su'] = $length_su;

            //在 ims_water_salegroup3_fans 表的拉新数
            $sql_fan = "select id,openid,sourceid,count(id) as laxin_count  from ims_water_salegroup3_fans where uniacid = neeedu and themeid = needt and  openid  in ($instr) and (sourceid is not null and sourceid <>'') GROUP BY  openid  order by laxin_count Desc limit 10";
            $sql_fan = str_ireplace($needu, $uniacid, $sql_fan);
            $sql_fan = str_ireplace($needt, $themeid, $sql_fan);
            $sql_fan_sqldata = \think\facade\Db::query($sql_fan);
            $arr['sql_fan_sqldata'] = $sql_fan_sqldata;
            $length_fan = count($sql_fan_sqldata);
            $arr['length_fan'] = $length_fan;

            for ($i = 0; $i < $worker_length; $i++) {
                //直推订单
                for ($j = 0; $j < $length_zhi; $j++) {
                    if ($sqldata[$i]['openid'] == $sql_zhi_record_sqldata[$j]['fopenid']) {
                        $sqldata[$i]['zhi_record_num'] = $sql_zhi_record_sqldata[$j]['zhi_count'];
                    }
                }


                for ($j = 0; $j < $length_su; $j++) {
                    if ($sqldata[$i]['openid'] == $sql_su_record_sqldata[$j]['sourceid']) {
                        $sqldata[$i]['su_record_num'] = $sql_su_record_sqldata[$j]['su_count'];
                    }
                }

//                for ($j = 0; $j < $length_fan; $j++) {
//                    if ($sqldata[$i]['openid'] == $sql_fan_sqldata[$j]['openid']) {
//                        $sqldata[$i]['worker_fan_num'] = $sql_fan_sqldata[$j]['laxin_count'];
//                    }
//                }

            }
            for ($i = 0; $i < $worker_length; $i++) {
                if (!isset($sqldata[$i]['zhi_record_num'])) {
                    $sqldata[$i]['zhi_record_num']= 0;
                }
                if (!isset($sqldata[$i]['su_record_num'])) {
                    $sqldata[$i]['su_record_num']= 0;
                }
            }



            //再次排序
            usort($sqldata, function ($a, $b) {
                if(isset($b['zhi_record_num']) && isset($a['zhi_record_num']) ){
                    if ($a['zhi_record_num'] !== $b['zhi_record_num']) {
                        return $b['zhi_record_num'] <=> $a['zhi_record_num'];
                    }    // 使用 PHP 7 的太空船操作符来进行比较

                    if(isset($b['su_record_num']) && isset($a['su_record_num']) ) {
                        return $b['su_record_num'] <=> $a['su_record_num'];
                    }
                }
            });

//            usort($sqldata, function ($a, $b) {
//                if(isset($b['su_record_num']) && isset($a['su_record_num']) ) {
//                    return $b['su_record_num'] <=> $a['su_record_num']; // 使用 PHP 7 的太空船操作符来进行比较
//                }
//            });

            $arr['sqldata'] = $sqldata;
            //处理下 “从0 开始取2个”
            $topdata = array();
            $qie = 3; //员工排行是取前3
            if ($countlength > $qie) {
                //想获取前2个为一组
                $topdata["one"] = array_slice($sqldata, 0, $qie);
                $topdata["two"] = array_slice($sqldata, $qie, count($sqldata) - $qie);
            } else {
                $topdata["one"] = $sqldata;
                $topdata['备足']= '小余3';
            }
            //$arr['sqldata'] = $sqldata;
            $arr['topdata'] = $topdata;
            $isDebug = \think\facade\App::isDebug();
            if ($isDebug) {
                $arr['sqlaction1'] = $actionsql;
                $arr['sqlaction2'] = $sql_zhi_record;
                $arr['sqlaction3'] = $sql_su_record;
                $arr['sqlaction4'] = $sql_fan;
            }

        } else {
            $arr['vdata'] = null;
        }
        return $arr;
    }

    //grouptop  团队排行
    //分析后sql语句不能共用一个是by openid 一个是by groups
    //方法可以共用 例如 xxxxx by （[openid|groups]）
    public function grouptop($uniacid, $themeid)
    {
        //找出所有groups 例如 大卫美术
        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "select id,openid,username,groups  from ims_water_salegroup3_worker where `status` = 1 and  uniacid = neeedu and  themeid = needt and (groups is not null and groups <> '') group by groups";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        $group_length = count($sqldata); //这里主要查所有的团队
        $instr = "";
        //团队的个数大于0 才去查订单表 才有意义
        $countlength = count($sqldata);
        $arr['lenth_sql'] = $countlength;
        if ($countlength > 0) {
            $arr['group_length'] = $group_length; //团队个数
            for ($i = 0; $i < $group_length; $i++) {
                $instr .= "'" . $sqldata[$i]['groups'] . "'" . ',';
            }
            $instr = substr($instr, 0, -1);
            //$arr['instr'] = $instr;
            //instr 查找条件

            //在 record 里把所有直推订单 按groups 的条件找出来
            $sql_g_zhi_record = " select id,openid,fopenid,sourceid,groups,sourceid,count(id) as g_zhi_count  from ims_water_salegroup3_record where uniacid = neeedu and  themeid = needt and (`status` = 1 or `status` = 2 )  and  (fopenid is not null  and fopenid <>'') and (groups is not null  and groups <>'') and groups in ($instr) GROUP BY  groups  order by g_zhi_count DESC";
            $sql_g_zhi_record = str_ireplace($needu, $uniacid, $sql_g_zhi_record);
            $sql_g_zhi_record = str_ireplace($needt, $themeid, $sql_g_zhi_record);
            $sql_g_zhi_record_sqldata = \think\facade\Db::query($sql_g_zhi_record);
            //$arr['sql_g_zhi_record_sqldata'] = $sql_g_zhi_record_sqldata;
            $length_g_zhi = count($sql_g_zhi_record_sqldata);
            //$arr['length_g_zhi'] = $length_g_zhi;

            //在 record 里把所有溯源订单搜出来 按groups 的条件找出来
            $sql_g_su_record = " select id,openid,fopenid,sourceid,groups,sourceid,count(id) as g_su_count  from ims_water_salegroup3_record where uniacid = neeedu  and themeid = needt and  (`status` = 1 or `status` = 2 ) and  (sourceid is not null and sourceid <>'')  and  (groups is not null  and groups <>'') and groups in ($instr) GROUP BY  groups  order by g_su_count DESC ";
            $sql_g_su_record = str_ireplace($needu, $uniacid, $sql_g_su_record);
            $sql_g_su_record = str_ireplace($needt, $themeid, $sql_g_su_record);
            $sql_g_su_record_sqldata = \think\facade\Db::query($sql_g_su_record);
            //$arr['sql_su_record_sqldata'] = $sql_g_su_record_sqldata;
            $length_g_su = count($sql_g_su_record_sqldata);
            //$arr['length_g_su'] = $length_g_su;

            //在 ims_water_salegroup3_fans 表的拉新数
            $sql_g_fan = "select id,openid,groups,sourceid,count(id) as g_laxin_count  from ims_water_salegroup3_fans where uniacid = neeedu  and themeid = needt  and  groups  in ($instr) and (sourceid is not null  and sourceid <> '') GROUP BY  groups  order by g_laxin_count DESC";
            $sql_g_fan = str_ireplace($needu, $uniacid, $sql_g_fan);
            $sql_g_fan = str_ireplace($needt, $themeid, $sql_g_fan);
            $sql_g_fan_sqldata = \think\facade\Db::query($sql_g_fan);
            //$arr['sql_fan_sqldata'] = $sql_g_fan_sqldata;
            $length_g_fan = count($sql_g_fan_sqldata);
            //$arr['length_g_fan'] = $length_g_fan;

            for ($i = 0; $i < $group_length; $i++) {
                //团队直推订单
                for ($j = 0; $j < $length_g_zhi; $j++) {
                    if ($sqldata[$i]['groups'] == $sql_g_zhi_record_sqldata[$j]['groups']) {
                        $sqldata[$i]['zhi_g_record_num'] = $sql_g_zhi_record_sqldata[$j]['g_zhi_count'];
                    }
                }
                //团队溯源订单
                for ($j = 0; $j < $length_g_su; $j++) {
                    if ($sqldata[$i]['groups'] == $sql_g_su_record_sqldata[$j]['groups']) {
                        $sqldata[$i]['su_g_record_num'] = $sql_g_su_record_sqldata[$j]['g_su_count'];
                    }
                }
                //网页访客数，即拉新数
                for ($j = 0; $j < $length_g_fan; $j++) {
                    if ($sqldata[$i]['groups'] == $sql_g_fan_sqldata[$j]['groups']) {
                        $sqldata[$i]['group_fan_num'] = $sql_g_fan_sqldata[$j]['g_laxin_count'];
                    }
                }
            }

            for ($i = 0; $i < $group_length; $i++) {
                if (!isset($sqldata[$i]['zhi_g_record_num'])) {
                    $sqldata[$i]['zhi_g_record_num']= 0;
                }
                if (!isset($sqldata[$i]['su_g_record_num'])) {
                    $sqldata[$i]['su_g_record_num']= 0;
                }

                if (!isset($sqldata[$i]['group_fan_num'])) {
                    $sqldata[$i]['group_fan_num']= 0;
                }
            }

            //再次排序
            usort($sqldata, function ($a, $b) {
                if(isset($b['zhi_g_record_num']) && isset($a['zhi_g_record_num']) ){
                    return $b['zhi_g_record_num'] <=> $a['zhi_g_record_num']; // 使用 PHP 7 的太空船操作符来进行比较
                }
            });

            //团队数据也要处理下
            $qie = 2; //员工排行是取前3
            if ($countlength > $qie) {
                //想获取前2个为一组
                $topdata["one"] = array_slice($sqldata, 0, $qie);
                $topdata["two"] = array_slice($sqldata, $qie, count($sqldata) - $qie);
            } else {
                $topdata["one"] = $sqldata;
            }
            $arr['sqldata'] = $sqldata;
            $arr['topdata'] = $topdata;
            $isDebug = \think\facade\App::isDebug();
            if ($isDebug) {
                $arr['sqlaction1_groups'] = $actionsql;
                $arr['sqlaction2_groups'] = $sql_g_zhi_record;
                $arr['sqlaction3_groups'] = $sql_g_su_record;
                $arr['sqlaction4_groups'] = $sql_g_fan;
            }
        } else {
            $arr['vdata'] = null;
        }
        return $arr;
    }

    //redtop
    public function redtop($uniacid, $themeid)
    {
        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "select id,uniacid,themeid,openid,`status`,nickname,avatar,redpackage from ims_water_salegroup3_fans where uniacid = neeedu  and themeid = needt and  `status`= 1 and (redpackage is not null and redpackage <> 0.00) ORDER BY redpackage DESC ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        $lenth_sqldata = count($sqldata);
        $arr['lenth_sqldata'] = $lenth_sqldata;
        if ($lenth_sqldata > 0) {
            $arr['sqldata'] = $sqldata;
        } else {
            $arr['vdata'] = null;
        }
        $isDebug = \think\facade\App::isDebug();
        if ($isDebug) {
            $arr['actionsql1'] = $actionsql;
        }
        return $arr;
    }
    //testime
    public function testime($str){
        $startTime = microtime(true);
        $sqldata = \think\facade\Db::query($str);
        $endTime = microtime(true);
        $arr['m-time-1'] = $endTime - $startTime;
        return  $arr;
    }
    //fetchpnames
    public function fetchpnames($pids){
        $instr= '';
        $len =  count($pids);
        for ($i = 0; $i <$len; $i++) {
            $instr .= "'" . $pids[$i] . "'" . ',';
        }
        $instr = substr($instr, 0, -1);

        $orginsql = " SELECT * from ims_uni_account where uniacid in ( $instr ) limit 100 ";
        $sqldata = \think\facade\Db::query($orginsql);

        $lenth_sqldata = count($sqldata);
        if ($lenth_sqldata > 0) {
            //$arr['sql'] = $orginsql;
            $arr['sqldata'] = $sqldata;
        }else{
            $arr['vdata'] = null;
        }
        //$arr['sql_str']= $orginsql;
        return $arr;

    }

    //yuyinone
    public function yuyinone($uniacid, $themeid,$recordid){
        $needu = 'neeedu';
        $needt = 'needt';
        $needr = 'needr';
        $orginsql = "select r.id,r.openid,r.fopenid,r.sourceid,r.groups,r.sourceid,w.username ,w.nickname ,r.nickname as rnickname,r.username as guanlian_username ,r.addtime,r.paytime from ims_water_salegroup3_record r
    left join
    ims_water_salegroup3_worker w on r.sourceid = w.openid  where r.uniacid = neeedu and  r.themeid = needt and r.id = needr  group by r.id  ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $orginsql = str_ireplace($needt, $themeid, $orginsql);
        $actionsql = str_ireplace($needr, $recordid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        $lenth_sqldata = count($sqldata);
        $arr['lenth_sqldata'] = $lenth_sqldata;
        if ($lenth_sqldata > 0) {
            $arr['sqldata'] = $sqldata;
        } else {
            $arr['vdata'] = null;
        }
        $isDebug = \think\facade\App::isDebug();
        if ($isDebug) {
            $arr['actionsql1'] = $actionsql;
        }
        $arr['x']='xx';
        return $arr;
    }

    //语音播放抓最新的10个订单 tentopOrder
    public function tentopOrder($uniacid, $themeid){
        $needu = 'neeedu';
        $needt = 'needt';
        $orginsql = "select r.id,r.openid,r.fopenid,r.sourceid,r.groups,r.sourceid,w.username,r.username as guanlian_username  from ims_water_salegroup3_record r
    left join
    ims_water_salegroup3_worker w on r.sourceid = w.openid  where r.uniacid = neeedu and  r.themeid = needt and (r.`status` = 1  ) group by r.id order by r.addtime desc  limit 20 ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($actionsql);
        $lenth_sqldata = count($sqldata);
        $arr['lenth_sqldata'] = $lenth_sqldata;
        if ($lenth_sqldata > 0) {
            $arr['sqldata'] = $sqldata;
        } else {
            $arr['vdata'] = null;
        }
        $isDebug = \think\facade\App::isDebug();
        if ($isDebug) {
            $arr['actionsql1'] = $actionsql;
        }
        $arr['x']='xx';
        return $arr;
    }

    //findbysn
    public function findbysn($uniacid, $themeid,$sn){
        $needu = 'neeedu';
        $needt = 'needt';

        $orginsql ="select * from ims_water_salegroup3_order where ordersn = $sn  ";

        $sqldata = \think\facade\Db::query($orginsql);

        $lenth_sqldata = count($sqldata);
        if ($lenth_sqldata > 0) {
            $arr['sql'] = $orginsql;
            $arr['sqldata'] = $sqldata;
        }else{
            $arr['vdata'] = null;
        }
        $arr['sql_str']= $orginsql;
        return $arr;
    }

    public function findbyrr($uniacid, $themeid,$rr){
        $needu = 'neeedu';
        $needt = 'needt';

        $orginsql ="select * from ims_water_salegroup3_record where id  = $rr  ";

        $sqldata = \think\facade\Db::query($orginsql);

        $lenth_sqldata = count($sqldata);
        if ($lenth_sqldata > 0) {
            $arr['sql'] = $orginsql;
            $arr['sqldata'] = $sqldata;
        }else{
            $arr['vdata'] = null;
        }
        $arr['sql_str']= $orginsql;
        return $arr;
    }
    //findbthemebyid
    public function findbthemebyid($themeid){
        $needu = 'neeedu';
        $needt = 'needt';

        $orginsql ="select * from ims_water_salegroup3_theme  where id  = $themeid  ";

        $sqldata = \think\facade\Db::query($orginsql);

        $lenth_sqldata = count($sqldata);
        if ($lenth_sqldata > 0) {
            $arr['sql'] = $orginsql;
            $arr['sqldata'] = $sqldata;
        }else{
            $arr['vdata'] = null;
        }
        $arr['sql_str']= $orginsql;
        return $arr;
    }

    //findOrder
    public function findOrder($uniacid, $themeid,$openid){
        $needu = 'neeedu';
        $needt = 'needt';
        $instr = "";
        $instr .= "'" .$openid. "'" . ',';
        $instr = substr($instr, 0, -1);

        $orginsql ="select * from ims_water_salegroup3_record where uniacid = neeedu  and themeid = needt  and fopenid in ( $instr ) ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $orginsql = str_ireplace($needt, $themeid, $orginsql);

        $sqldata = \think\facade\Db::query($orginsql);

        $lenth_sqldata = count($sqldata);
        if ($lenth_sqldata > 0) {
            $arr['sql'] = $orginsql;
            $arr['sqldata'] = $sqldata;
        }else{
            $arr['vdata'] = null;
        }
        $arr['sql_str']= $orginsql;
        return $arr;

    }

    //eventslist 18事件大屏
    public function eventslist($uniacid, $themeid)
    {
        $needu = 'neeedu';
        $needt = 'needt';
        $limit_sql = 60;
        $orginsql = "select r.id,r.uniacid,r.themeid,r.`status`,r.avatar,r.nickname,r.way,r.username as guanlian_username,r.sourceid as rsourceid,r.addtime,w.username as laiyuan_username from ims_water_salegroup3_record r left join ims_water_salegroup3_worker w on r.sourceid = w.openid  where r.uniacid = neeedu  and r.themeid = needt and  (r.`status` = 1 or r.`status` = 0 ) group by r.id order by r.addtime desc ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $actionsql = str_ireplace($needt, $themeid, $orginsql);
        $actionsql .= " limit " . $limit_sql;
        $startTime = microtime(true);
        $sqldata = \think\facade\Db::query($actionsql);
        $endTime = microtime(true);
        $arr['m-time-1'] = $endTime - $startTime;
        $arr['原来长度'] = count($sqldata);
        $lenth_sqldata = count($sqldata);
        $arr['length_sqldata'] = $lenth_sqldata;
        if ($lenth_sqldata > 0) {
            //对事件名称遍历修改 例如 status = 0 事件名称为 "等待支付"  event_name
            //额外加一遍历,时间转换 addtime_text;
            foreach ($sqldata as &$recorditem) {
                if ($recorditem['addtime'] == 0) {
                    $recorditem['addtime_text'] = "--";
                } else {
                    $recorditem['addtime_text'] = date('m-d H:i:s', $recorditem['addtime']);
                }
                switch ($recorditem['status']) {
                    case 0:
                        $recorditem['event_name'] = '等待支付';
                        break;
                    case 1:
                        $recorditem['event_name'] = '支付成功';
                        break;
                    default:
                        $recorditem['event_name'] = '等待处理';
                }
                unset($recorditem);
            }

            $arr['sqldata'] = $sqldata;
            $sql_comm = " select  c.id,c.uniacid,c.themeid,c.`status`,c.avatar,c.nickname,c.fee,c.type,c.ordersn,c.addtime,o.username as  guanlian_username, f.province,w.username as laiyuan_username from ims_water_salegroup3_comm c
            left join ims_water_salegroup3_fans f on c.openid = f.sourceid  
            left join ims_water_salegroup3_worker w on w.openid = f.sourceid
            left join ims_water_salegroup3_order o on c.ordersn = o.ordersn
            where c.uniacid = neeedu and c.themeid = needt   and c.`status` = 1 group by id order by c.addtime desc ";

            $sql_comm = "	SELECT 
    sq.id,sq.uniacid,sq.openid,sq.themeid,sq.`status`,sq.avatar,sq.nickname,sq.fee,sq.type,sq.ordersn,sq.addtime, sq.province,sq.sourceid,sq.ordersn,w.username as laiyuan_username,o.ordersn as oordersn,o.nickname as guanlian_username
FROM (
    select   c.id,c.uniacid,c.openid,c.themeid,c.`status`,c.avatar,c.nickname,c.fee,c.type,c.ordersn,c.addtime, f.province,f.sourceid from ims_water_salegroup3_comm c            join ims_water_salegroup3_fans f on c.openid = f.sourceid  
	 where c.uniacid = neeedu and c.themeid = needt   and c.`status` = 1  GROUP BY c.id ORDER BY c.addtime desc limit 60	
) AS sq
Left JOIN  ims_water_salegroup3_worker w on w.openid = sq.sourceid   
left join ims_water_salegroup3_order o on sq.ordersn = o.ordersn  GROUP BY sq.id  ";

            $sql_comm = str_ireplace($needu, $uniacid, $sql_comm);
            $sql_comm = str_ireplace($needt, $themeid, $sql_comm);

            //$sql_len_conm = "SELECT COUNT(id) as sumlen FROM (" . $sql_comm . ")  AS temp_record_view";
            $startTime = microtime(true);
            //没用到就不查  $sql_len_sqldata = \think\facade\Db::query($sql_len_conm);
            $endTime = microtime(true);
            $arr['m-time-2'] = $endTime - $startTime;


            $comm_limmit = $lenth_sqldata;
            $sql_comm .= "limit " . $comm_limmit;
            $startTime = microtime(true);
            $sql_comm_sqldata = \think\facade\Db::query($sql_comm);
            $endTime = microtime(true);
            $arr['m-time-3'] = $endTime - $startTime;
            $arr['sql_comm_sqldata'] = $sql_comm_sqldata;
            $length_comm = count($sql_comm_sqldata);
            $arr['length_comm'] = $length_comm;
            if ($length_comm > 0) {
                foreach ($sql_comm_sqldata as &$item) {  // 使用引用符号 & 以便直接修改原始数组中的对象
                    // 根据 type 的值设置状态
                    if ($item['addtime'] == 0) {
                        $item['addtime_text'] = "---";
                    } else {

                        $item['addtime_text'] = date('m-d H:i:s', $item['addtime']);

                    }

                    if ($item['fee'] > 0) {
                        $item['event_name'] = '获得' . $item['fee'] . '元红包';
                    }

                    switch ($item['type']) {
                        case 0:
                            $item['way'] = '一级开团';
                            break;
                        case 1:
                            $item['way'] = '一级参团';
                            break;
                        case 2:
                            $item['way'] = '二级推荐';
                            break;
                        case 3:
                            $item['way'] = '分享红包';
                            break;
                        case 4:
                            $item['way'] = '拉新红包';
                            break;
                        case 5:
                            $item['way'] = '团长红包';
                            break;
                        case 6:
                            $item['way'] = '溯源红包';
                            break;
                        case 7:
                            $item['way'] = '团员红包';
                            break;
                        case 8:
                            $item['way'] = '累计红包';
                            break;
                        case 9:
                            $item['way'] = '未知状态1';
                            break;
                        case 10:
                            $item['way'] = '未知状态2';
                            break;
                        case 11:
                            $item['way'] = '未知状态3';
                            break;
                        case 12:
                            $item['way'] = '满团红包';
                            break;
                        case 13:
                            $item['way'] = '浏览红包';
                            break;
                        case 14:
                            $item['way'] = '自购红包';
                            break;
                        case 15:
                            $item['way'] = '梯次红包';
                            break;
                        default:
                            $item['way'] = '未知状态';
                    }

                }
                $combinedArray = array_merge($sqldata, $sql_comm_sqldata);
                usort($combinedArray, function ($a, $b) {
                    return $b['addtime'] <=> $a['addtime']; // 使用 PHP 7 的太空船操作符来进行比较
                });
                unset($item);
                //$arr['sort_data'] = $combinedArray;  //
                $arr['现在长度'] = count($combinedArray);
                $arr['sqldata'] = $combinedArray;
            }


        } else {
            $arr['vdata'] = null;
            $sql_comm = '没必要要查红包了';
        }
        $isDebug = \think\facade\App::isDebug();
        if ($isDebug) {
            $arr['actionsql1'] = $actionsql;
            $arr['actionsql2'] = $sql_comm;
        }
        $arr['isDebug'] = $isDebug;
        //$arr['ask表'] = "ims_water_salegroup3_record 和 ims_water_salegroup3_worker";
        return $arr;
    }


    //查找海报对应的两张图
    public function findhaibao_touxiang($uniacid,$themeid,$openid)
    {
        $needu = 'neeedu'; //平台ID uniacid
        $needt = 'needt';  //活动themeid t
        //$needo = "needo";  //openid
        $orginsql = "SELECT * from ims_water_salegroup3_fans  where uniacid = neeedu and themeid = needt and  openid = '". $openid  ."' limit 1 ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $orginsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($orginsql);
        $arr['sql'] = $orginsql;
        $arr['sqldata'] = $sqldata;
        $arr['ask'] = "findhaibao";
        return $arr;
    }

    //活动相关包括head findhaibao_config
    public function findhaibao_config($uniacid,$themeid)
    {
        $needu = 'neeedu'; //平台ID uniacid
        $needt = 'needt';  //活动themeid t
        //DB设计有问题 活动themeid 又变成aic了
        $orginsql = "SELECT * from ims_water_salegroup3_config  where uniacid = neeedu and `key` = needt   limit 1 ";
        $orginsql = str_ireplace($needu, $uniacid, $orginsql);
        $orginsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($orginsql);
        $arr['sql'] = $orginsql;
        $arr['sqldata'] = $sqldata;
        $arr['ask'] = "findhaibao_config";
        return $arr;
    }

    public function findhaibao_config_t($themeid)
    {
        $needt = 'needt';  //活动themeid t
        //DB设计有问题 活动themeid 又变成aic了
        $orginsql = "SELECT * from ims_water_salegroup3_config  where  `key` = needt   ";
        $orginsql = str_ireplace($needt, $themeid, $orginsql);
        $sqldata = \think\facade\Db::query($orginsql);
        $arr['sql'] = $orginsql;
        $arr['sqldata'] = $sqldata;
        $arr['ask'] = "findhaibao_config";
        return $arr;
    }

    //the_worklist https://static.kancloud.cn/manual/thinkphp6_0/1037533  说可以转array toArray()
    public function the_worklist($uniacid,$themeid)
    {
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_worker")
        ->where('uniacid',$uniacid)
        ->where('themeid',$themeid)
//            ->fetchSql(true)
        ->order('id','desc')
            ->select();

        $arr['sqldata'] = $sqldata;
        $arr['ask'] = "the_worklist";
        return $arr;
    }



    //edit_work_by_openid
    public function edit_work_by_openid($uniacid,$themeid,$openid,$identity,$status)
    {

        $sqldata = \think\facade\Db::name("ims_water_salegroup3_worker")
            ->where('uniacid',$uniacid)
            ->where('themeid',$themeid)
            ->where('openid',$openid)
//            ->fetchSql(true)
            ->update(['identity'=>$identity,'status'=>$status] );

        $arr['sqldata'] = $sqldata;
        $arr['ask'] = "edit_work_by_openid";
        return $arr;
    }

    //getdong39
    public function gettidby39like($key)
    {
        //ims_water_salegroup3_worker
        //`ims_water_salegroup3_order
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_order")
            ->where('detailid',$key)
            ->order('id','desc')
            ->select();

        $arr['sqldata'] = $sqldata;
        $arr['ask'] = "gettidby39like";
        return $arr;
    }

    //抢购 rab
    //不可能出现在拼团项目里
    // ims_water_salegroup3_order
    public function gettidby39like_rab($key)
    {
        //ims_water_groupbuy3_order
        //`ims_water_groupbuy3_order
        $sqldata = \think\facade\Db::table("ims_water_groupbuy3_order")
            ->where('detailid',$key)
            ->order('id','desc')
            ->select();

        $arr['sqldata'] = $sqldata;
        $arr['ask'] = "gettidby39like";
        return $arr;
    }

    //phpabcoupons
    //业务1 下单人
    public function phpabc_buynum($pid,$aid,$cur_openid){
        $num = $sqldata = \think\facade\Db::table("ims_water_salegroup3_record")
            ->where('uniacid',$pid)
            ->where('themeid',$aid)
            ->whereIn('status',[1,2])
            ->where('openid',$cur_openid)
            ->count('openid');
        return $num;
    }

    //phpabc_buynum_new
    public function phpabc_buynum_new($pid, $aid, $cur_openid){
        $num = $sqldata = \think\facade\Db::table("ims_water_salegroup3_record")
            ->where('uniacid',$pid)
            ->where('themeid',$aid)
            ->whereIn('status',[1,2])
            ->where('openid',$cur_openid)
            ->where(function ($query) {
                $query->where('option', 'like', '%新%')
                    ->whereOr('option', '');
            })

            ->count('openid');
        return $num;
    }


    //邀请，人下单 ims_water_salegroup3_order
    public function phpabc_invitenum($pid,$aid,$cur_openid){
        $where['status'] = array('in', array(1,2));
        $num = $sqldata = \think\facade\Db::table("ims_water_salegroup3_record")
            ->where('uniacid',$pid)
            ->where('themeid',$aid)
            ->whereIn('status',[1,2])
            ->where('fopenid',$cur_openid)
            ->count('fopenid');
        return $num;
    }

    //统计只有新 才有奖励 phpabc_invitenum_new
    public function phpabc_invitenum_new($pid,$aid,$cur_openid){
        $where['status'] = array('in', array(1,2));
        $num = $sqldata = \think\facade\Db::table("ims_water_salegroup3_record")
            ->where('uniacid',$pid)
            ->where('themeid',$aid)
            ->whereIn('status',[1,2])
            ->where('fopenid',$cur_openid)
            ->where(function ($query) {
                $query->where('option', 'like', '%新%')
                    ->whereOr('option', '');
            })
            ->count('fopenid');
        return $num;
    }



    //ims_water_salegroup3_fans
    public function phpabc_fansnum($pid,$aid,$cur_openid){
        $num = $sqldata = \think\facade\Db::table("ims_water_salegroup3_fans")
            ->distinct(true)
            ->field("openid")
            ->where('popenid',$cur_openid)
            ->where('uniacid',$pid)
            ->where('themeid',$aid)
            ->count('openid');
        return $num;
    }

    //findOrderBytid2
    public function findOrderBytid2($tid2){
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_order")
            ->where('ordersn',$tid2)
            ->order('id','desc')
            ->select();
//        $arr['sqldata'] = $sqldata;
//        $arr['ask'] = "findOrderBytid2";
        return $sqldata[0];
    }

    //findOrderBydetailid
    public function findOrderBydetailid($detailid){
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_order")
            ->where('detailid',$detailid)
            ->order('id','desc')
            ->select();
//        $arr['sqldata'] = $sqldata;
//        $arr['ask'] = "findOrderBytid2";
        return $sqldata[0];
    }


    //findThemebyid
    public function findThemebyid($id){
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_theme")
            ->where('id',$id)
            ->order('id','desc')
            ->select();
//        $arr['sqldata'] = $sqldata;
//        $arr['ask'] = "findOrderBytid2";
        return $sqldata[0];
    }

    public function findOrderByShortid($shortorderid){
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_record")
            ->where('id',$shortorderid)
            ->order('id','desc')
            ->select();
//        $arr['sqldata'] = $sqldata;
//        $arr['ask'] = "findOrderBytid2";
        return $sqldata[0];
    }







    //findRecordByxx
    public function findRecordByxx($xx){
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_record")
            ->where('id',$xx)
            ->order('id','desc')
            ->select();
//        $arr['sqldata'] = $sqldata;
//        $arr['ask'] = "findRecordByxx";
        return $sqldata[0];
    }

    //findRecordByNoid 找同组订单
    public function findRecordByNoid($noid){
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_record")
            ->where('noid',$noid)
            ->order('id','desc')
            ->select();
//        $arr['sqldata'] = $sqldata;
//        $arr['ask'] = "findRecordByxx";
        return $sqldata;
    }




    //findpidaidforfengbyphone()
    public function findpidaidforfengbyphone_1($phone){
        $sqldata = \think\facade\Db::table("ims_users")
            ->where('username',$phone)
            ->order('uid','desc')
            ->select();
        return $sqldata;
    }
    //findpidaidforfengbyphone_2
    public function findpidaidforfengbyphone_2($uid){
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_theme")
            ->where('uid',$uid)
            ->select();
        return $sqldata;
    }

    public function findpidaidforfengbyaid($aid){
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_theme")
            ->where('id',$aid)
            ->select();
        return $sqldata;
    }



    //findpidaidforfengbyphone_3
    public function findpidaidforfengbyphone_3($uid,$aid){
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_theme")
            ->where('uid',$uid)
            ->where('id',$aid)
            ->select();
        return $sqldata;
    }

    //findphonebyaid
    public function findphonebyaid_1($aid){
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_theme")
            ->where('id',$aid)
            ->select();
        return $sqldata;
    }

    //findphonebyaid_2
    public function findphonebyaid_2($uid){
        $sqldata = \think\facade\Db::table("ims_users")
            ->where('uid',$uid)
            ->select();
        return $sqldata;
    }

    //findchouuser
    public function findchouuser($uid,$arr){
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_record")
            ->wherein('openid',$arr)
            ->where('uniacid',53)
            ->select();
        return $sqldata;
    }


    public function findRecordDateByuu($uu){
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_record")
            ->where('billcode',$uu)
            ->order('id','desc')
            ->select();
        $arr['sqldata'] = $sqldata;
        $arr['ask'] = "findRecordByxx";
        return $arr;
    }

    //


    //getapp
    public function getapp(){
        $sqldata = \think\facade\Db::table("ims_account_wxapp")
            ->select();
        $arr['sqldata'] = $sqldata;
        $arr['ask'] = "ims_account_wxapp";
        return $arr;
    }
    //getwechats
    public function getwechats(){
        $sqldata = \think\facade\Db::table("ims_account_wechats")
            ->select();
        $arr['sqldata'] = $sqldata;
        $arr['ask'] = "ims_account_wechats";
        return $arr;
    }

    //getappbypid
    public function getappbypid($pid){
        $sqldata = \think\facade\Db::table("ims_account_wxapp")
            ->where('uniacid',$pid)
            ->select();
        $arr['sqldata'] = $sqldata;
        $arr['ask'] = "ims_account_wxapp";
        return $arr;
    }

    public function findpaymsgbypid($pid){
        $sqldata = \think\facade\Db::table("ims_uni_account_modules")
            ->where('uniacid',$pid)
            ->select();

        return $sqldata;
    }

    //findaidsbypid
    public function findaidsbypid($pid){
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_theme")
            ->where('uniacid',$pid)
            ->select();

        return $sqldata;
    }

    //pcaids_totalfee
    public function pcaids_totalfee($themeids_arr)
    {
        $sqldata = Db::table("ims_water_salegroup3_record")
            ->whereIn('themeid',  $themeids_arr)
            ->whereIn('status',[1,2])
            ->group('themeid')
            ->field('themeid, SUM(fee) AS total_fee')
            ->select()
            ->toArray();

        return $sqldata;
    }

    //pc 红包统计 pcaids_commfee
    public function pcaids_commfee($themeids_arr)
    {
        $sqldata = Db::table("ims_water_salegroup3_comm")
            ->whereIn('themeid',  $themeids_arr)
            ->whereIn('status',[1])
            ->group('themeid')
            ->field('themeid, SUM(fee) AS comm_fee')
            ->select()
            ->toArray();

        return $sqldata;
    }

    //pcaids_other
    public function pcaids_other($themeids_arr)
    {
        $sqldata = Db::table("ims_water_salegroup3_theme")
            ->whereIn('id',  $themeids_arr)
            ->group('id')
            ->field('id as themeid, sum(paynum) as total_paynum,sum(fanssum) AS total_fanssum,sum(scansum) as total_scansum')
            ->select()
            ->toArray();

        return $sqldata;
    }

    //findcasebypid
    public function findcasebypid($pid)
    {
        $sqldata = Db::table("ims_water_salegroup3_theme")
            ->where('uniacid',  $pid)
            ->whereIn('iscase',  [1])
            ->select()
            ->toArray();

        return $sqldata;
    }


    //findaidsbyuid
    public function findaidsbyuid($uid,$cpage,$listrows)
    {
        $sqldata = Db::table("ims_water_salegroup3_theme")
            ->where('uid',  $uid)
            ->order('id', 'desc')
            ->paginate([
                'list_rows' => $listrows,
                'page'      => $cpage,
            ]);

        return $sqldata;
    }

    public function findformsetbypidandaid($pid,$aid){
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_set")
            ->where('uniacid',$pid)
            ->where('key',$aid)
            ->select();
        return $sqldata;
    }


    //findthemeinfobypidaid
    public function findthemeinfobypidaid($pid,$aid){
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_theme")
            ->where('uniacid',$pid)
            ->where('id',$aid)
            ->select();
        return $sqldata;
    }

    //findconfigbypidaid
    public function findconfigbypidaid($pid,$aid){
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_config")
            ->where('uniacid',$pid)
            ->where('key',$aid)
            ->select();
        return $sqldata;
    }

    //pcfindordernum
    public function pcfindordernum($lgdate){

        //DB设计有问题 活动themeid 又变成aic了
//        $orginsql = SELECT COUNT(*) AS order_count  FROM ims_water_salegroup3_order WHERE addtime >= "
//            . strtotime( $lgdate )
//            . " AND addtime < "
//            . strtotime("$lgdate")";

        $orginsql = "SELECT COUNT(*) AS order_count  FROM ims_water_salegroup3_order WHERE addtime >=  strtotime( \"$lgdate\" )  AND addtime <=  strtotime(\"$lgdate\") ";

        return $orginsql;
        $sqldata = \think\facade\Db::query($orginsql);
        $arr['sql'] = $orginsql;
        $arr['sqldata'] = $sqldata;
        $arr['ask'] = "pcfindordernum";
        return $arr;
    }


    public function pcfindgroup($pid,$aid)
        {
            $sqldata = \think\facade\Db::table("ims_water_salegroup3_config")
                ->where('uniacid',$pid)
                ->where('key',$aid)
                ->select();
            return $sqldata;
        }


        //xiav1
    public function xiav1($id)
    {
        $sqldata = \think\facade\Db::table("ims_water_salegroup3_record_data")
            ->where('id',$id)
            ->select();
        return $sqldata;
    }

    //find_orders_bypidaid
    public function find_orders_bypidaid($pid,$aid)
    {
        $sqldata = Db::table("ims_water_salegroup3_order")
            ->where('themeid',  $aid)
            ->where('uniacid',$pid)
            ->field(['tid' ,'detailid']) // ✅ 指定你要的字段
            ->select()
            ->toArray();

        return $sqldata;
    }

    //findobj_forfix
    public function findobj_forfix($oid)
    {
        $sqldata = Db::table("ims_water_salegroup3_order")
            ->where('detailid',  $oid)
            ->field(['id','tid' ,'transaction_id','detailid']) // ✅ 指定你要的字段
            ->select()
            ->toArray();
        return $sqldata;
    }

    //finddidpid_byphone
    public function finddidpid_byphone($phone)
    {
        $result = Db::table('ims_water_salegroup3_theme')
            ->alias('t')
            ->join('ims_users u', 't.uid = u.uid', 'LEFT')
            ->where('u.username', '=', $phone)
            ->group('t.uniacid')           // 按 t.uniacid 分组
            ->field('t.*')
            ->select();
        return $result;
    }

    //find_f_name
    public function find_f_name($uniacid,$themeid,$openid){
        $r = Db::table('ims_water_salegroup3_fans')
            ->where('uniacid',$uniacid)
            ->where('themeid',$themeid)
            ->where('openid',$openid)
            ->select();
        return $r;
    }

    //搜索 订单-主要是姓名
    public function findrecordsbykey($pid,$aid,$key){
        $inkey = "%$key%";
        $r = Db::table('ims_water_salegroup3_record')
            ->where('uniacid',$pid)
            ->where('themeid',$aid)
            ->whereIn('status',[1,2])
            ->where(function ($query) use ($inkey) {
                $query->where('id', 'like', $inkey)
                    ->whereOr('nickname', 'like',$inkey)
                    ->whereOr('mobile','like',$inkey);
            })
            ->field(['id','uniacid' ,'themeid','nickname','mobile']) // ✅ 指定你要的字段
            ->select();
        return $r;
    }


    public function timediff2($begin_time,$end_time)

    {

        if($begin_time < $end_time){

            //现在时间  活动时间还没到

            $starttime = $begin_time;

            $endtime = $end_time;

        }else{

            //b > e 说明 活动已经结束了



            $res = array("day" => 0,"hour" => 0,"min" => 0,"sec" => 0);

            return $res;

        }

        //计算天数

        $timediff = $endtime-$starttime;

        $days = intval($timediff/86400);

        //计算小时数

        $remain = $timediff%86400;

        $hours = intval($remain/3600);

        //计算分钟数

        $remain = $remain%3600;

        $mins = intval($remain/60);

        //计算秒数

        $secs = $remain%60;

        $res = array("day" => $days,"hour" => $hours,"min" => $mins,"sec" => $secs);

        return $res;

    }
}
